package com.netease;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.lark.oapi.Client;
import com.lark.oapi.core.enums.AppType;
import com.lark.oapi.core.request.RequestOptions;
import com.lark.oapi.core.response.RawResponse;
import com.lark.oapi.core.token.AccessTokenType;
import com.lark.oapi.core.utils.Jsons;
import com.lark.oapi.okhttp.*;
import com.lark.oapi.service.ext.enums.FileTypeEnum;
import com.lark.oapi.service.ext.model.CreateFileReq;
import com.lark.oapi.service.ext.model.CreateFileReqBody;
import com.lark.oapi.service.ext.model.CreateFileResp;
import com.lark.oapi.service.im.v1.enums.MsgTypeEnum;
import com.lark.oapi.service.im.v1.model.ext.MessageText;
import com.lark.oapi.service.sheets.v3.model.QuerySpreadsheetSheetReq;
import com.lark.oapi.service.sheets.v3.model.QuerySpreadsheetSheetResp;
import org.apache.catalina.filters.ExpiresFilter;

import java.io.IOException;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

/**
 * @projectName: modeling-dev
 * @package: com.netease
 * @className: RawApiCall
 * @author: aochong
 * @description: 原生API调用方式
 * @date: 2023/6/15 16:42
 * @version: 1.0
 */
public class RawApiCall {

    public static final String appId = "cli_a4cb9785b3fb900b";
    public static final String appSecret = "gRPLohyHdiflCMn1oKPKohBvrpcJ4vGO";

    public static void sendMsg() throws Exception {

        // 构建client
        Client client = Client.newBuilder(appId, appSecret)
                .appType(AppType.SELF_BUILT) // 设置app类型，默认为自建
                .logReqAtDebug(true)
                .build();

        // 构建http body
        Map<String, Object> body = new HashMap<>();
        body.put("receive_id", "ou_c245b0a7dff2725cfa2fb104f8b48b9d");
        body.put("content", MessageText.newBuilder()
                .atUser("ou_155184d1e73cbfb8973e5a9e698e74f2", "Tom")
                .text("test content")
                .build());
        body.put("msg_type", MsgTypeEnum.MSG_TYPE_TEXT);

        // 发起请求
        RawResponse resp = client.post(
                "https://open.feishu.cn/open-apis/im/v1/messages?receive_id_type=open_id"
                , body
                , AccessTokenType.Tenant);

        // 处理结果
        System.out.println(resp.getStatusCode());
        System.out.println(Jsons.DEFAULT.toJson(resp.getHeaders()));
        System.out.println(new String(resp.getBody()));
        System.out.println(resp.getRequestID());
    }

    /**
     * @param :
     * @return String
     * @author aochong
     * @description 获取飞书token
     * @date 2023/6/15 16:55
     */
    public static String getTenantToken() throws Exception {
        // 构建client
        Client client = Client.newBuilder(appId, appSecret)
                .appType(AppType.SELF_BUILT) // 设置app类型，默认为自建
                .logReqAtDebug(true)
                .build();

        // 发起请求
        Map<String, Object> body = new HashMap<>();
        body.put("app_id", appId);
        body.put("app_secret", appSecret);
        RawResponse resp = client.post(
                "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal"
                , body
                , AccessTokenType.None);

        // 处理结果
//        System.out.println(resp.getStatusCode());
//        System.out.println(Jsons.DEFAULT.toJson(resp.getHeaders()));
//        System.out.println(new String(resp.getBody()));
//        System.out.println(resp.getRequestID());
        JSONObject jsonObject = JSONObject.parseObject(new String(resp.getBody()));
        return jsonObject.get("tenant_access_token").toString();
    }

    /**
     * @param :
     * @return String
     * @author aochong
     * @description 获取工作表
     * @date 2023/6/15 16:55
     */
    public static JSONObject getWorksheet(String spreadsheets) throws Exception {
        // 构建client
        Client client = Client.newBuilder(appId, appSecret)
                //如需SDK自动管理租户Token的获取与刷新,可删除该行
                .disableTokenCache()
                .build();

        // 创建请求对象
        QuerySpreadsheetSheetReq req = QuerySpreadsheetSheetReq.newBuilder()
                .spreadsheetToken(spreadsheets)
                .build();

        // 发起请求
        // 如开启了Sdk的token管理功能，就无需调用 RequestOptions.newBuilder().tenantAccessToken("t-xxx").build()来设置租户token了
        QuerySpreadsheetSheetResp resp = client.sheets().spreadsheetSheet().query(req, RequestOptions.newBuilder()
                .tenantAccessToken(getTenantToken())
                .build());

        // 处理服务端错误
        if(!resp.success()) {
            System.out.println(String.format("code:%s,msg:%s,reqId:%s", resp.getCode(), resp.getMsg(), resp.getRequestId()));
            return null;
        }

        // 业务数据处理
//        System.out.println(Jsons.DEFAULT.toJson(resp.getData()));
        return JSONObject.parseObject(Jsons.DEFAULT.toJson(resp.getData()));
    }

    /**
     * @param :
     * @return String
     * @author aochong
     * @description 调用飞书获取单元格值
     * @date 2023/6/15 16:55
     */
    public static JSONObject getCellValue(String spreadsheets,String range) throws Exception {
        OkHttpClient client = new OkHttpClient().newBuilder()
                .build();
        MediaType mediaType = MediaType.parse("");
        Request request = new Request.Builder()
                .url("https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/" + spreadsheets + "/values/" + range + "?valueRenderOption=FormattedValue")
                .method("GET", null)
                .addHeader("Authorization", "Bearer " + getTenantToken())
                .build();
        Response response = client.newCall(request).execute();
//        System.out.println(JSONObject.parseObject(response.body().string()));

        return JSONObject.parseObject(response.body().string());
    }

    /**
     * @param :
     * @return int
     * @author aochong
     * @description 获取sheet页最大行数
     * @date 2023/6/16 11:30
     */
    public static int getSheetMaxRowCnt(String title, Map<String,JSONObject> sheetMap) {

        int result = 0;

        try {
            if (sheetMap.containsKey(title)) {
                JSONObject sheet = sheetMap.get(title);
                JSONObject gridProperties = sheet.getJSONObject("grid_properties");
                result = Integer.parseInt(gridProperties.getString("row_count"));
            }else {
                System.out.println("模型表不存在！请先在在线文档先创建模型表！");
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param :
     * @return int
     * @author aochong
     * @description 获取sheet页最大列数
     * @date 2023/6/16 11:30
     */
    public static int getSheetMaxColumnCnt(String title, Map<String,JSONObject> sheetMap) {

        int result = 0;

        try {
            if (sheetMap.containsKey(title)) {
                JSONObject sheet = sheetMap.get(title);
                JSONObject gridProperties = sheet.getJSONObject("grid_properties");
                result = Integer.parseInt(gridProperties.getString("column_count"));
            }else {
                System.out.println("模型表不存在！请先在在线文档先创建模型表！");
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param :
     * @return int
     * @author aochong
     * @description 获取sheet页唯一ID
     * @date 2023/6/16 11:30
     */
    public static String getSheetId(String title, Map<String,JSONObject> sheetMap) {

        String result = "";

        try {
            if (sheetMap.containsKey(title)) {
                JSONObject sheet = sheetMap.get(title);
                result = sheet.getString("sheet_id");
            }else {
                System.out.println("模型表不存在！请先在在线文档先创建模型表！");
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * @param spreadsheets:
     * @return JSONObject
     * @author aochong
     * @description 获取电子表格信息
     * @date 2023/6/26 15:36
     */
    public static JSONObject getDocumentInfo(String spreadsheets) throws Exception {

        OkHttpClient client = new OkHttpClient().newBuilder()
                .build();
        MediaType mediaType = MediaType.parse("text/plain");
        RequestBody body = RequestBody.create(mediaType, "");
        Request request = new Request.Builder()
                .url("https://open.feishu.cn/open-apis/sheets/v3/spreadsheets/" + spreadsheets)
                .method("GET", null)
                .addHeader("Authorization", "Bearer " + getTenantToken())
                .addHeader("Cookie", "_csrf_token=bf672b2a56b0177e94a5d19b9ded53c5adf375e9-1686822830; lark_oapi_csrf_token=ZBVCWe6enmyO2u1dZx/mXSbB3qhce01zyYm2DPc41dM=; ngw=true")
                .build();
        Response response = client.newCall(request).execute();

        return JSONObject.parseObject(response.body().string());
    }

    public static void main(String arg[]) throws Exception {

        StringBuilder sb = new StringBuilder("drop table if exists ");
        StringBuilder schema = new StringBuilder();

//        System.out.println(getTenantToken());
//        System.out.println(getWorksheet("B2ZVsROSzhZTHktrbmLcVDlTnrc"));

        JSONObject workSheet = getWorksheet("shtcn3jrHRth4LWPbLzCwmXMyVq");
//        System.out.println(workSheet);
        JSONArray sheets = workSheet.getJSONArray("sheets");
        HashMap<String, JSONObject> sheetMap = new HashMap<>();
        for (int i = 0; i < sheets.size(); i++) {
            JSONObject sheet = sheets.getJSONObject(i);
            sheetMap.put(sheet.getString("title"),sheet);
        }

        String docTitle = getDocumentInfo("B2ZVsROSzhZTHktrbmLcVDlTnrc").getJSONObject("data").getJSONObject("spreadsheet").getString("title");

        if (docTitle.contains("EDW")){
            schema.append("dw.");
        }else if(docTitle.contains("ODS")){
            schema.append("ods.");
        }else if(docTitle.contains("DWS")){
            schema.append("dws.");
        }else if(docTitle.contains("ADS")){
            schema.append("ads.");
        }

        //追加schema
        sb.append(schema);

        //获取表名，表注释信息
        JSONObject tblHeaderJson = getCellValue("B2ZVsROSzhZTHktrbmLcVDlTnrc", getSheetId("内审项目开展情况明细",sheetMap) + "!B3:C4");
        System.out.println(tblHeaderJson);
        JSONArray headers = tblHeaderJson.getJSONObject("data").getJSONObject("valueRange").getJSONArray("values");

        String tblEngName = "";
        String tblComment = "";

        for (int i = 0; i < headers.size(); i++) {
            if (i == 0) {
                tblEngName = headers.getString(i).replaceAll("\\[|\\]|\"","").split(",")[1];
                sb.append(tblEngName).append(";");
                sb.append("\n").append("create table if not exists ").append(schema).append(tblEngName).append("(");
            }else if(i == headers.size()-1){
                tblComment = headers.getString(i).replaceAll("\\[|\\]|\"","").split(",")[1];
            }
        }

        //获取字段信息
        JSONObject tblBodyJson = getCellValue("B2ZVsROSzhZTHktrbmLcVDlTnrc", getSheetId("内审项目开展情况明细",sheetMap) + "!B1:D" + getSheetMaxRowCnt("内审项目开展情况明细",sheetMap));
        JSONArray fields = tblBodyJson.getJSONObject("data").getJSONObject("valueRange").getJSONArray("values");


        int index = 0;
        for (int i = 0; i < fields.size(); i++) {
            String[] rowCol = fields.getString(i).replaceAll("\\[|\\]|\"", "").split(",");

            if ("字段注释".equals(rowCol[0])) {
                index = i+1;
            }
        }


        for (int i = 0; i < fields.size(); i++) {

            String[] rowCol = fields.getString(i).replaceAll("\\[|\\]|\"", "").split(",");

            //当大于 字段注释所在行 开始拼接字段信息
            if (i > index) {
                if (i < fields.size()-1){
                    if (rowCol[0] != null && rowCol[1] != null && rowCol[2] != null) {
                        sb.append("\n").append("\t").append(rowCol[1]).append(" ").append(rowCol[2]).append(",").append("--").append(rowCol[0]);
                    }else{
                        sb.append("\n").append("\t").append("请检查B:D列是否不为空!").append(" ").append(",").append("--").append("请检查B:D列是否不为空!");
                    }
                }else{
                    if (rowCol[0] != null && rowCol[1] != null && rowCol[2] != null) {
                        sb.append("\n").append("\t").append(rowCol[1]).append(" ").append(rowCol[2]).append("--").append(rowCol[0]);
                    }else {
                        sb.append("\n").append("\t").append("请检查B:D列是否不为空!").append(" ").append("--").append("请检查B:D列是否不为空!");
                    }
                }
            }
        }

        sb.append("\n").append(");");

        //拼接表注释信息
        sb.append("\n").append("\n").append("comment on table ").append(schema).append(tblEngName).append(" is ").append("'").append(tblComment).append("'").append(";");
        //遍历追加字段注释信息
        for (int i = 0; i < fields.size(); i++) {

            String[] rowCol = fields.getString(i).replaceAll("\\[|\\]|\"", "").split(",");

            sb.append("\n").append("comment on column ").append(schema).append(tblEngName).append(".").append(rowCol[1]).append(" is '").append(rowCol[0]).append("';");
        }

        System.out.println(sb);

    }
}
